﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using GPS.GPS_DAL;
using Microsoft.Office.Interop.Word;

namespace GPS.Controllers.GPS_NghiepVu
{
    public class GPS_KhoaHocController : Controller
    {
        #region Onit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(ViewModelDKH_DT viewModel)
        {
            string DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = DVGTVT_MA;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, viewModel.pKH_DVGTVT_MA);
            viewModel.pKH_DVGTVT_MA = getdvgtvt_Ma;

            //string MaKhoa = null;
            //string TenKhoa = null;
            //GPS.DAL.Check_User.Substring_KhoaHoc(viewModel.pKH_MA, ref MaKhoa, ref TenKhoa);
            //viewModel.pKH_TEN = TenKhoa;
            //viewModel.pKH_MA = MaKhoa;
            ViewBag.Message = string.Empty;
            var khoahocs = Singleton<GPS_HocVienDAO>.Inst.GPS_GETDS_KHOAHOC_HOCVIEN1(viewModel.pKH_DVGTVT_MA, viewModel.pKH_MA);
            ViewBag.KH_MAs = new SelectList(khoahocs, "KH_MA", "KH_TEN");

            ViewBag.DVGTVT_MAs = new SelectList(_db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40" && o.DVGTVT_ACTIVE == "1"), "DVGTVT_MA", "DVGTVT_TEN");
            ViewBag.DEFAULT_VALUE_DDL = CategoryCommon.DEFAULT_VALUE_DDL;
            ViewBag.NAM_VALUE_DDL = CategoryCommon.NAM_VALUE_DDL;
        }
        #endregion

        #region Index
        public ActionResult Index(ViewModelDKH_DT SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            List<CrudModelFORMDKH> model = new List<CrudModelFORMDKH>();
            if (SearchModel.SearchButton == "Xuất Excel")
            {

                try
                {
                    model = GetListData(SearchModel);

                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\DanhSachKhoahoc.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\DanhSachKhoahoc_Export.xlsx"));
                    // Using the template to create the newFile...

                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];



                        int recordIndex = 4;
                        for (int i = 0; i < model.Count; i++)
                        {
                            myWorksheet.InsertRow(recordIndex + i, 1, 4);

                            myWorksheet.Cells[recordIndex + i, 1].Value = model[i].STT;
                            myWorksheet.Cells[recordIndex + i, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;



                            myWorksheet.Cells[recordIndex + i, 2].Value = model[i].KH_TEN;
                            myWorksheet.Cells[recordIndex + i, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 3].Value = model[i].DVGTVT_TEN;
                            myWorksheet.Cells[recordIndex + i, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 4].Value = model[i].KH_HANGGPLX_MA;
                            myWorksheet.Cells[recordIndex + i, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                            myWorksheet.Cells[recordIndex + i, 5].Value = model[i].KH_HANGDT_MA;
                            myWorksheet.Cells[recordIndex + i, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                            myWorksheet.Cells[recordIndex + i, 6].Value = model[i].KH_SOQDKG;
                            myWorksheet.Cells[recordIndex + i, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            //myWorksheet.Cells[recordIndex + i, 7].Value = model[i].KH_NGAYQDKG_TEXT;
                            //myWorksheet.Cells[recordIndex + i, 7].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 7].Value = model[i].KH_NGAYKG_TEXT;
                            myWorksheet.Cells[recordIndex + i, 7].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 8].Value = model[i].KH_NGAYBG_TEXT;
                            myWorksheet.Cells[recordIndex + i, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 9].Value = model[i].KH_NGAYTHI_TEXT;
                            myWorksheet.Cells[recordIndex + i, 9].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 10].Value = model[i].KH_NGAYSH_TEXT;
                            myWorksheet.Cells[recordIndex + i, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            myWorksheet.Cells[recordIndex + i, 11].Value = model[i].KH_TONGHV;
                            myWorksheet.Cells[recordIndex + i, 11].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                            myWorksheet.Cells[recordIndex + i, 12].Value = model[i].KH_MOTA;
                            myWorksheet.Cells[recordIndex + i, 12].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;

                            string modelRange = "A" + (recordIndex + i).ToString() + ":L" + (recordIndex + i).ToString();
                            myWorksheet.Cells[modelRange].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            //  recordIndex++;

                        }

                        myWorksheet.DeleteRow(model.Count + recordIndex);

                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\DanhSachKhoaHoc_Export.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("DanhSachKhoaHoc_{0}", DateTime.Now.ToShortDateString().Replace("/", "_") + ".xlsx"));
                }
                catch (Exception ex)
                {
                    ViewBag.Message = string.Format("Hello {0}.\\nCurrent Date and Time: {1}", "TEST", DateTime.Now.ToString());
                    return RedirectToAction("Index");
                }
            }
            else if (SearchModel.SearchButton == "In kế hoạch đào tạo")
            {
                //khoi tao Word app:
                Microsoft.Office.Interop.Word.Application _app = null;
                Microsoft.Office.Interop.Word.Document _doc = null;
                object _pathFile = null;
                object ob = System.Reflection.Missing.Value;

                try
                {

                    // DataSet ds = (new GPS_DAO()).GPS_DAOTAO_KHDT("40001", "40001K17B2037");
                    DataSet ds = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_KHDT"
                                        , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_DVGTVT_MA) ? DBNull.Value : (object)SearchModel.pKH_DVGTVT_MA, ParameterDirection.Input)
                                        , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_MA) ? DBNull.Value : (object)SearchModel.pKH_MA, ParameterDirection.Input)
                                        , new OracleParameter("PRETHEADER", OracleDbType.RefCursor, ParameterDirection.Output)
                                        , new OracleParameter("PRETKHDT_GIOHOC", OracleDbType.RefCursor, ParameterDirection.Output)
                                        , new OracleParameter("PRETKHDT_LICHHOC", OracleDbType.RefCursor, ParameterDirection.Output)
                                        , new OracleParameter("PRETGVLTHUYET", OracleDbType.RefCursor, ParameterDirection.Output)
                                        , new OracleParameter("PRETGVTHUCHANH", OracleDbType.RefCursor, ParameterDirection.Output)
                                          );
                    string _HANGGPLX_MA = ds.Tables[0].Rows[0]["KH_HANGGPLX_MA"].ToString();
                   
                    //GiangTD3 - 2017-08-29: 
                    string filename = "KeHoachDaoTao_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                    String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                    String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/KHDT_Templ-HangB.dotx");

                    if (_HANGGPLX_MA == "C")
                        TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/KHDT_Templ-HangC.dotx");

                    _pathFile = TEMPLATE_FILE_NAME;
                    _app = new Microsoft.Office.Interop.Word.Application();
                    _app.Visible = false;

                    _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);

                   
                    // thông tin chung khóa học
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        System.Data.DataTable vDataTable = ds.Tables[0];

                        Dictionary<string, string> dicKHOAHOC = new Dictionary<string, string>();
                        dicKHOAHOC.Add("DVGTVT_TEN", vDataTable.Rows[0]["DVGTVT_TEN"].ToString().ToUpper());

                        dicKHOAHOC.Add("KH_MA_TEN", vDataTable.Rows[0]["KH_MA_TEN"].ToString());
                        dicKHOAHOC.Add("KH_HANGGPLX_MA", vDataTable.Rows[0]["KH_HANGGPLX_MA"].ToString());
                        dicKHOAHOC.Add("KH_THOIGIANDT", vDataTable.Rows[0]["KH_THOIGIANDT"].ToString());
                        dicKHOAHOC.Add("KH_TONGHV", vDataTable.Rows[0]["KH_TONGHV"].ToString());

                       

                        var _now = DateTime.Now;
                        dicKHOAHOC.Add("DD", (_now.Day < 10 ? "0" + _now.Day.ToString() : _now.Day.ToString()));
                        dicKHOAHOC.Add("MM", (_now.Month < 10 ? "0" + _now.Month.ToString() : _now.Month.ToString()));
                        dicKHOAHOC.Add("YYYY", _now.Year.ToString());

                        foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                        {
                            string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                            if (dicKHOAHOC.ContainsKey(fieldName))
                            {
                                field.Select();
                                _app.Selection.TypeText(dicKHOAHOC[fieldName]);
                            }
                        }
                    }

                    //Thông tin giờ học
                    if (ds.Tables[1].Rows.Count > 0)
                    {

                        System.Data.DataTable vDataTable = ds.Tables[1];
                        Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[2];


                        int lenrow = vDataTable.Rows.Count;
                        int lencol = vDataTable.Columns.Count;

                        for (int i = 0; i < lenrow; ++i)
                        {

                            tbl.Rows.Add(ref ob);

                            for (int j = 0; j < lencol; ++j)
                            {


                                tbl.Cell(i + 2, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(i + 2, j + 1).Range.Font.Size = 10F;

                            }

                        }
                    }
                    _doc.Tables[2].Rows[ds.Tables[1].Rows.Count + 2].Delete();


                    //Thông tin phân phối lịch học
                    if (ds.Tables[2].Rows.Count > 3)
                    {

                        System.Data.DataTable vDataTable = ds.Tables[2];
                        int lenrow = vDataTable.Rows.Count;
                        int lencol = vDataTable.Columns.Count;

                        Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[3];

                        //set width
                        // tbl.Columns[2].SetWidth(_app.Application.CentimetersToPoints(5f), Microsoft.Office.Interop.Word.WdRulerStyle.wdAdjustNone);

                        //Add column:
                        object c8 = System.Reflection.Missing.Value;
                        DataRow rTuNgayDenNgay = ds.Tables[2].Rows[0];
                        var lencol_temp = 0;
                        for (int i = 0; i < lencol; i++)
                        {
                            if (!rTuNgayDenNgay[i].Equals(DBNull.Value) && rTuNgayDenNgay[i] != null && !string.IsNullOrWhiteSpace(rTuNgayDenNgay[i].ToString()))
                            {
                                tbl.Columns.Add(ref c8);
                                lencol_temp++;

                            }
                        }
                        lencol = lencol_temp;

                        //fill data to 3 row đầu
                        var countmerge = 0;
                        var temp1 = "";
                        var _cellindex = 0;
                        var _temp = 0;
                        var _temp2 = 0;

                        for (int j = 1; j < lencol; j++)
                        {
                            temp1 = vDataTable.Rows[0][j].ToString();
                            if (j == 0)
                            {
                                _cellindex = j + 1 + _temp2;
                            }
                            else
                            {
                                _cellindex = j + 1 + _temp2;
                            }

                            if (!string.IsNullOrWhiteSpace(temp1))
                            {
                                if (j < lencol - 1)
                                {
                                    for (int k = j + 1; k < lencol; k++)
                                    {
                                        _temp = k;
                                        if (vDataTable.Rows[0][k].ToString() == temp1)
                                        {
                                            countmerge++;

                                        }
                                        else
                                        {
                                            break;

                                        }
                                    }

                                    _temp2 = _temp2 - countmerge;
                                    if (countmerge > 0)
                                        tbl.Cell(1, _cellindex).Merge(tbl.Cell(1, countmerge + _cellindex));


                                    tbl.Cell(1, _cellindex).Range.Text = temp1;
                                    tbl.Cell(1, _cellindex).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                    tbl.Cell(1, _cellindex).Range.Font.Size = 10F;
                                    tbl.Cell(1, _cellindex).Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                    countmerge = 0;
                                    j = _temp - 1;

                                }
                                else
                                {
                                    tbl.Cell(1, _cellindex).Range.Text = temp1;
                                    tbl.Cell(1, _cellindex).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                    tbl.Cell(1, _cellindex).Range.Font.Size = 10F;
                                    tbl.Cell(1, _cellindex).Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                    // j++;
                                }
                            }


                        }

                        //fill 2 row tiếp theo:
                        for (int i = 1; i < 3; ++i)
                        {

                            for (int j = 1; j < lencol; ++j)
                            {


                                tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(i + 1, j + 1).Range.Font.Size = 10F;

                            }

                        }

                        //fill còn lại
                        for (int i = 3; i < lenrow; ++i)
                        {

                            tbl.Rows.Add(ref ob);

                            for (int j = 0; j < lencol; ++j)
                            {


                                tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(i + 1, j + 1).Range.Font.Size = 10F;

                            }

                        }
                    }

                    //if (ds.Tables[2].Rows.Count > 3)
                    //    _doc.Tables[3].Rows[ds.Tables[2].Rows.Count + 1].Delete();
                    //else
                    //    _doc.Tables[3].Rows[4].Delete();


                    //thông tin lý thuyết
                    if (ds.Tables[3].Rows.Count > 0)
                    {

                        System.Data.DataTable vDataTable = ds.Tables[3];
                        Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[4];

                        int lenrow = vDataTable.Rows.Count;
                        int lencol = vDataTable.Columns.Count;

                        for (int i = 0; i < lenrow; ++i)
                        {
                            tbl.Rows.Add(ref ob);
                            for (int j = 0; j < lencol; ++j)
                            {
                                tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// + vDataTable.Rows[i][1].ToString();
                                tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(i + 1, j + 1).Range.Font.Size = 11.5F;

                                tbl.Cell(i + 1, j + 1).Range.Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderBottom].LineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDashDotDot;
                                tbl.Cell(i + 1, j + 1).Range.Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderBottom].LineWidth = Microsoft.Office.Interop.Word.WdLineWidth.wdLineWidth050pt;
                            }




                        }

                    }

                    //  _doc.Tables[4].Rows[ds.Tables[3].Rows.Count + 1].Delete();

                    //thông tin thực hành
                    if (ds.Tables[4].Rows.Count > 0)
                    {

                        System.Data.DataTable vDataTable = ds.Tables[4];
                        Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[5];

                        int lenrow = vDataTable.Rows.Count;
                        int lencol = vDataTable.Columns.Count;

                        for (int i = 0; i < lenrow; ++i)
                        {

                            tbl.Rows.Add(ref ob);

                            for (int j = 0; j < lencol; ++j)
                            {


                                tbl.Cell(i + 2, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(i + 2, j + 1).Range.Font.Size = 11.5F;

                            }

                        }
                    }
                    _doc.Tables[5].Rows[ds.Tables[4].Rows.Count + 2].Delete();


                    try
                    {

                        object filenamesave = GENERATED_FILE_NAME;
                        _doc.SaveAs2(ref filenamesave);
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (_doc != null)
                        {
                            _doc.Close(ref ob, ref ob, ref ob);
                            _doc = null;
                        }
                        if (_app != null)
                        {
                            _app.Quit(ref ob, ref ob, ref ob);
                            _app = null;
                        }

                    }

                    return File(GENERATED_FILE_NAME, "application/docx", filename);



                    //CrudModelDKH_KHDT _model = BindDataExport_KeHoachDaoTao(SearchModel.pKH_DVGTVT_MA, SearchModel.pKH_MA);
                    //byte[] contentFile = null;
                    //contentFile = GPS.ENTITYS.EXPORTWORD_KHOAHOC_KHDT.EXPORT_KHOAHOC_KHDT(Server.MapPath("~/Templates/GPS_Word/KeHoachVaLichThiSatHach.docx"), _model, "DocxGenericReport");
                    //string nameOutput = string.Format("KeHoachVaLichThiSatHach_{0}_{1}.docx", SearchModel.pKH_MA, DateTime.Now.ToShortDateString().Replace("/", "_"));
                    //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);


                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (_doc != null)
                    {
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                    }
                    if (_app != null)
                    {
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }
                }

            }
            else if (SearchModel.SearchButton == "In lưu lượng đào tạo")
            {
                try
                {
                    if (string.IsNullOrEmpty(SearchModel.pKH_DVGTVT_MA))
                    {
                        SearchModel.pKH_DVGTVT_MA = "40";
                    }
                    List<ExportExcel_LuuLuong> _model = new List<ExportExcel_LuuLuong>();
                    DataSet ds = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_LUULUONGDT_2"
                                                              , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_DVGTVT_MA) ? DBNull.Value : (object)SearchModel.pKH_DVGTVT_MA, ParameterDirection.Input)
                                                              , new OracleParameter("pRet", OracleDbType.RefCursor, ParameterDirection.Output)
                                                             );

                    _model = COMMON.Helpers.MapDataTableToList<ExportExcel_LuuLuong>(ds.Tables[0]);
                    var _luuluongDT = _model[0].DVGTVT_LLDT;

                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\LuuLuongDaoTao.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\LuuLuongDaoTao_Export.xlsx"));
                    // Using the template to create the newFile...
                    string _TenDVGTVT = "";
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];

                        //Them luu luong thuc te:

                        var _rowindex = 6;
                       
                        for(int j=5;j<=52;j++)
                        {
                            myWorksheet.Cells[_rowindex, j].Value = _luuluongDT;
                            myWorksheet.Cells[_rowindex, j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
                        }

                        var _lst_HangDT = _model.Select(r => new ExportExcel_LuuLuong { HANG_DT = r.HANG_DT })
                                                     .GroupBy(x => x.HANG_DT)
                                                     .Select(x => x.First()).ToList().OrderBy(o => o.HANG_DT).ToList();

                        Dictionary<string, int> dictViTriHangDT = new Dictionary<string, int>(); //Key:HANGDT, Value: Position row HANGDT



                        int recordIndex = 8;
                        for (int i = 0; i < _lst_HangDT.Count; i++)
                        {
                            dictViTriHangDT.Add(_lst_HangDT[i].HANG_DT, 8 + i);
                            // dictTongHangDT.Add(_lst_HangDT[i].HANG_DT, 0);

                            myWorksheet.InsertRow(8 + i, 1, 7);

                            for (int j = 5; j < 53; j++)
                            {
                                myWorksheet.Cells[8 + i, j].Value = "0";
                                myWorksheet.Cells[8 + i, j].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
                            }
                            string cellmerge = string.Format("A{0}:D{1}", 8 + i, 8 + i);
                            myWorksheet.Cells[cellmerge].Merge = true;
                            myWorksheet.Cells[cellmerge].Value = "Lưu lượng hạng " + _lst_HangDT[i].HANG_DT;

                            recordIndex++;
                        }
                        myWorksheet.View.FreezePanes(8 + _lst_HangDT.Count + 1, 4);

                        var _lst_KhoaHoc = _model.Select(r => new ExportExcel_LuuLuong
                        {
                            KH_TEN = r.KH_TEN,
                            KH_NGAYKG = r.KH_NGAYKG,
                            KH_NGAYBG = r.KH_NGAYBG,
                            KH_TONGHV = r.KH_TONGHV
                         ,
                            HANG_DT = r.HANG_DT,
                            TUAN_KG = r.TUAN_KG,
                            TUAN_BG = r.TUAN_BG,
                            THANG_KG = r.THANG_KG,
                            THANG_BG = r.THANG_BG
                        })
                        .GroupBy(x => x.KH_TEN)
                        .Select(x => x.First());

                        recordIndex++;
                        var _cellStart = 0;
                        var _cellEnd = 0;
                        var _rowTong = 7;
                        var _valueTong = 0;
                        foreach (var item in _lst_KhoaHoc)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.KH_TEN == null ? "" : item.KH_TEN.ToString();
                            myWorksheet.Cells[recordIndex, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
                            myWorksheet.Cells[recordIndex, 2].Value = item.KH_NGAYKG == null ? "" : item.KH_NGAYKG.ToString();
                            myWorksheet.Cells[recordIndex, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
                            myWorksheet.Cells[recordIndex, 3].Value = item.KH_NGAYBG == null ? "" : item.KH_NGAYBG.ToString();
                            myWorksheet.Cells[recordIndex, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
                            myWorksheet.Cells[recordIndex, 4].Value = item.KH_TONGHV == null ? "0" : item.KH_TONGHV.ToString();
                            myWorksheet.Cells[recordIndex, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;

                            //add số lượng học viên:

                            //select 4*(4) - (4-1) from dual  
                            _cellStart = int.Parse(item.THANG_KG) * 4 - (4 - int.Parse(item.TUAN_KG)) + 4; // 4 này là 4 cell đầu, làm như này sẽ tổng quát hơn, khi thêm cell vào đầu sẽ không ảnh hưởng
                            _cellEnd = int.Parse(item.THANG_BG) * 4 - (4 - int.Parse(item.TUAN_BG)) + 4; // 4 này là 4 cell đầu, làm như này sẽ tổng quát hơn, khi thêm cell vào đầu sẽ không ảnh hưởng
                            var _distance = _cellEnd - _cellStart;
                            for (int j = 0; j <= _distance; j++)
                            {
                                myWorksheet.Cells[recordIndex, _cellStart + j].Value = item.KH_TONGHV == null ? "0" : item.KH_TONGHV.ToString();
                                //tính tổng của từng hạng:
                                var _rowHangDT = dictViTriHangDT[item.HANG_DT];
                                var _strValueHangDT = myWorksheet.Cells[_rowHangDT, _cellStart + j].GetValue<string>();
                                var _valueHangDT = 0;
                                if (!string.IsNullOrWhiteSpace(_strValueHangDT))
                                    _valueHangDT = int.Parse(_strValueHangDT.Trim()) + Convert.ToInt32(item.KH_TONGHV);
                                else
                                    _valueHangDT = 0 + Convert.ToInt32(item.KH_TONGHV);

                                myWorksheet.Cells[_rowHangDT, _cellStart + j].Value = _valueHangDT;

                                //tính tổng của toàn cột:
                                var _strValueTong = myWorksheet.Cells[_rowTong, _cellStart + j].GetValue<string>();

                                if (!string.IsNullOrWhiteSpace(_strValueTong))
                                    _valueTong = int.Parse(_strValueTong.Trim()) + Convert.ToInt32(item.KH_TONGHV);
                                else
                                    _valueTong = 0 + Convert.ToInt32(item.KH_TONGHV);
                                myWorksheet.Cells[_rowTong, _cellStart + j].Value = _valueTong;


                            }
                            string modelRange = "A" + (recordIndex).ToString() + ":AZ" + (recordIndex).ToString();
                            myWorksheet.Cells[modelRange].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            myWorksheet.Cells[modelRange].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                            recordIndex++;
                        }
                        _TenDVGTVT = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pKH_DVGTVT_MA && x.DVGTVT_MAQL == "40").DVGTVT_TEN;
                        myWorksheet.Cells[1, 1].Value = _TenDVGTVT;
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\LuuLuongDaoTao_Export.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("LuuLuongDaoTao_{0}_{1}", _TenDVGTVT, DateTime.Now.ToShortDateString().Replace("/", "_") + ".xlsx"));
                }
                catch (Exception ex)
                {
                    ViewBag.Message = string.Format("Hello {0}.\\nCurrent Date and Time: {1}", "TEST", DateTime.Now.ToString());
                    return RedirectToAction("Index");
                }
            }
            else if (SearchModel.SearchButton == "In tiến độ đào tạo")
            {
                try
                {
                    if (SearchModel.pNAM == null)
                        SearchModel.pNAM = Convert.ToDecimal(DateTime.Now.Year);

                    DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_TIENDODT"
                                                             , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_DVGTVT_MA) ? DBNull.Value : (object)SearchModel.pKH_DVGTVT_MA, ParameterDirection.Input)
                                                             , new OracleParameter("pNAM", OracleDbType.Varchar2, SearchModel.pNAM == null ? DBNull.Value : (object)SearchModel.pNAM, ParameterDirection.Input)
                                                             , new OracleParameter("pRet", OracleDbType.RefCursor, ParameterDirection.Output)
                                                            );
                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\TienDoDaoTao.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\TienDoDaoTao_Export.xlsx"));
                    // Using the template to create the newFile...
                    string _TenDVGTVT = "";
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        List<ExportExcel_TienDo> _model_DataListTienDo = COMMON.Helpers.MapDataTableToList<ExportExcel_TienDo>(ds.Tables[0]);
                        if (_model_DataListTienDo == null)
                            _model_DataListTienDo = new List<ExportExcel_TienDo>();
                        //GiangTD3 -2017-09-10
                        //--. Tinh Thang start:(row 3, col 5)

                        DateTime startDate = new DateTime(Convert.ToInt32(SearchModel.pNAM), 1, 1);
                        DateTime endDate = new DateTime(Convert.ToInt32(SearchModel.pNAM), 12, 31);
                        Dictionary<int, DateRange> dict = GPS_Library.GET_WEEK_DATERANGE(startDate, endDate);
                        var Weeks = Week.GetWeeks(startDate, endDate).Where(o => o.WeekYear == Convert.ToInt32(SearchModel.pNAM)).ToList();


                        var Months = Weeks.GroupBy(info => info.MonthOfWeek_CompareDetal)
                              .Select(group => new
                              {
                                  MonthOfWeek_CompareDetal = group.Key,
                                  Count = group.Count()
                              }).OrderBy(x => x.MonthOfWeek_CompareDetal)
                              ;

                        var _rowMerge = 3;
                        var _colmergeFrom = 5;
                        var _colmergeTo = 5;
                        foreach (var m in Months)
                        {
                            _colmergeTo = _colmergeFrom + m.Count - 1;

                            myWorksheet.Cells[_rowMerge, _colmergeFrom, _rowMerge, _colmergeTo].Merge = true;
                            myWorksheet.Cells[_rowMerge, _colmergeFrom, _rowMerge, _colmergeTo].Value = "Tháng " + m.MonthOfWeek_CompareDetal.ToString();
                            myWorksheet.Cells[_rowMerge, _colmergeFrom, _rowMerge, _colmergeTo].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                            _colmergeFrom = _colmergeFrom + m.Count;
                        }

                        //--2.Tinh Tuan

                        // var _wcount = 1;
                        var rowTuan = 5;
                        var _wTU_NGAY = string.Empty;
                        var _wDEN_NGAY = string.Empty;
                        foreach (var w in Weeks)
                        {
                            _wTU_NGAY = GPS_Library.Get_ddMM(w.StartOfWeek);
                            _wDEN_NGAY = GPS_Library.Get_ddMM(w.EndOfWeek);
                            myWorksheet.Cells[rowTuan, 4 + w.WeekNumber].Value = _wTU_NGAY + Environment.NewLine + _wDEN_NGAY;
                            myWorksheet.Cells[rowTuan, 4 + w.WeekNumber].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                            myWorksheet.Cells[rowTuan, 4 + w.WeekNumber].Style.WrapText = true;
                        }

                        //--3. Fill khoa hoc and lich hoc
                        var KhoaHocs = _model_DataListTienDo.Select(x => new ExportExcel_TienDo
                        {
                            KH_TEN = x.KH_TEN
                          ,
                            GV_HOTEN = x.GV_HOTEN
                          ,
                            KH_TONGHV = x.KH_TONGHV
                          ,
                            THANG = x.THANG
                          ,
                            TUAN_HOC = x.TUAN_HOC
                        }).GroupBy(x => x.KH_TEN).Select(x => x.First()).ToList();

                        int recordIndex = 8;
                        List<ExportExcel_TienDo> _lstLichHocByKhoaHoc = null;
                        int _week = 1;
                        for (int i = 0; i < KhoaHocs.Count(); i++)
                        {
                            myWorksheet.InsertRow(recordIndex + i, 1, 7);
                            myWorksheet.Cells[recordIndex + i, 1].Value = (i + 1);
                            myWorksheet.Cells[recordIndex + i, 2].Value = KhoaHocs[i].KH_TEN;
                            myWorksheet.Cells[recordIndex + i, 3].Value = KhoaHocs[i].GV_HOTEN;
                            myWorksheet.Cells[recordIndex + i, 4].Value = KhoaHocs[i].KH_TONGHV;
                            myWorksheet.Cells[recordIndex + i, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                            _lstLichHocByKhoaHoc = _model_DataListTienDo.Where(o => o.KH_TEN == KhoaHocs[i].KH_TEN).ToList();
                            for (int j = 0; j < _lstLichHocByKhoaHoc.Count(); j++)
                            {
                                if (_lstLichHocByKhoaHoc[j].KH_LH_TUNGAY != null)
                                {
                                    _week = new WeekDate(_lstLichHocByKhoaHoc[j].KH_LH_TUNGAY.Value).WeekNumber;
                                    var _valCell = myWorksheet.Cells[recordIndex + i, 4 + _week].Value;
                                    if (_valCell == null || string.IsNullOrWhiteSpace(_valCell.ToString()))
                                    {
                                        myWorksheet.Cells[recordIndex + i, 4 + _week].Value = _lstLichHocByKhoaHoc[j].KH_LH_GDOAN_MA;
                                    }
                                    else
                                    {
                                        if (_valCell.ToString() != _lstLichHocByKhoaHoc[j].KH_LH_GDOAN_MA)
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Value = _valCell + "," + _lstLichHocByKhoaHoc[j].KH_LH_GDOAN_MA;
                                    }
                                    switch (_lstLichHocByKhoaHoc[j].KH_LH_GDOAN_MA.Trim())
                                    {
                                        case "H"://Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
                                            //ws.Cells["A1:B1"].Style.Fill.BackgroundColor.SetColor(colFromHex);

                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Green);

                                            break;
                                        case "T":
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
                                            break;
                                        case "Đ":
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
                                            break;
                                        case "KT":
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                                            myWorksheet.Cells[recordIndex + i, 4 + _week].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
                                            break;

                                    }
                                }


                            }
                            string modelRange = "A" + (recordIndex).ToString() + ":BF" + (recordIndex).ToString();
                            myWorksheet.Cells[modelRange].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
                            myWorksheet.Cells[modelRange].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
                            myWorksheet.Cells[modelRange].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
                            myWorksheet.Cells[modelRange].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;

                            //  recordIndex++;
                        }
                        myWorksheet.DeleteRow(KhoaHocs.Count() + 8);
                        myWorksheet.DeleteRow(7);
                        //END

                        _TenDVGTVT = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pKH_DVGTVT_MA && x.DVGTVT_MAQL == "40").DVGTVT_TEN;
                        myWorksheet.Cells[1, 1].Value = _TenDVGTVT;
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\TienDoDaoTao_Export.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("TienDoDaoTao_{0}_{1}.xlsx", _TenDVGTVT, DateTime.Now.ToShortDateString().Replace("/", "_")));



                }
                catch (Exception ex)
                {
                    TempData["Message"] = ex.ToString();
                    return RedirectToAction("Index");
                }
            }
            else if (SearchModel.SearchButton == "In kế hoạch sát hạch")
            {
                //khoi tao Word app:
                Microsoft.Office.Interop.Word.Application _app = null;
                Microsoft.Office.Interop.Word.Document _doc = null;
                object _pathFile = null;
                object ob = System.Reflection.Missing.Value;

                try
                {

                    //GiangTD3 - 2017-08-29: 
                    string filename = "KeHoachSatHach_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                    String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                    String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/DKSHLX_Template.dotx");



                    _pathFile = TEMPLATE_FILE_NAME;
                    _app = new Microsoft.Office.Interop.Word.Application();
                    _app.Visible = false;

                    _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);

                    // DataSet ds = (new GPS_DAO()).GPS_DAOTAO_KHDT("40001", "40001K17B2037");
                    DataSet ds = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_KEHOACH_SATHACH"
                                        , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_DVGTVT_MA) ? DBNull.Value : (object)SearchModel.pKH_DVGTVT_MA, ParameterDirection.Input)
                                        , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(SearchModel.pKH_MA) ? DBNull.Value : (object)SearchModel.pKH_MA, ParameterDirection.Input)
                                        , new OracleParameter("PTU_NGAY", OracleDbType.Date, SearchModel.pKH_NGAYQDKG_TU == null ? DBNull.Value : (object)SearchModel.pKH_NGAYQDKG_TU, ParameterDirection.Input)
                                        , new OracleParameter("PDEN_NGAY", OracleDbType.Date, SearchModel.pKH_NGAYQDKG_DEN == null ? DBNull.Value : (object)SearchModel.pKH_NGAYQDKG_DEN, ParameterDirection.Input)
                                        , new OracleParameter("pRet1", OracleDbType.RefCursor, ParameterDirection.Output)
                                        , new OracleParameter("pRet2", OracleDbType.RefCursor, ParameterDirection.Output)

                                          );
                    // thông tin chung DVGTVT
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        System.Data.DataTable vDataTable = ds.Tables[0];

                        Dictionary<string, string> dicKHOAHOC = new Dictionary<string, string>();
                        dicKHOAHOC.Add("DVGTVT_TEN", vDataTable.Rows[0]["DVGTVT_TEN"].ToString().ToUpper());
                        dicKHOAHOC.Add("DVGTVT_TEN_KHONGHOA", vDataTable.Rows[0]["DVGTVT_TEN"].ToString());
                        if (vDataTable.Rows[0]["SO_QD_GP"] != null && !vDataTable.Rows[0]["SO_QD_GP"].Equals(DBNull.Value))
                            dicKHOAHOC.Add("SO_QD_GP", vDataTable.Rows[0]["SO_QD_GP"].ToString());
                        if (vDataTable.Rows[0]["NGAY_CP"] != null && !vDataTable.Rows[0]["NGAY_CP"].Equals(DBNull.Value))
                            dicKHOAHOC.Add("NGAY_CP", vDataTable.Rows[0]["NGAY_CP"].ToString());


                        var _now = DateTime.Now;
                        dicKHOAHOC.Add("dd", (_now.Day < 10 ? "0" + _now.Day.ToString() : _now.Day.ToString()));
                        dicKHOAHOC.Add("mm", (_now.Month < 10 ? "0" + _now.Month.ToString() : _now.Month.ToString()));
                        dicKHOAHOC.Add("yyyy", _now.Year.ToString());

                        foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                        {
                            string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                            if (dicKHOAHOC.ContainsKey(fieldName))
                            {
                                field.Select();
                                _app.Selection.TypeText(dicKHOAHOC[fieldName]);
                            }
                        }
                    }

                    //Thông tin kế hoạch sát hạch 
                    if (ds.Tables[1].Rows.Count > 0)
                    {

                        System.Data.DataTable vDataTable = ds.Tables[1];
                        Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[2];

                        int lenrow = vDataTable.Rows.Count;
                        int lencol = vDataTable.Columns.Count;

                        for (int i = 0; i < lenrow; ++i)
                        {

                            tbl.Rows.Add(ref ob);
                            tbl.Cell(i + 2, 1).Range.Text = (i + 1).ToString();
                            for (int j = 0; j < lencol; ++j)
                            {


                                tbl.Cell(i + 2, j + 2).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                                //tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                //tbl.Cell(i + 2, j + 1).Range.Font.Size = 10F;

                            }

                        }
                    }
                    // _app.Selection.Rows[ds.Tables[1].Rows.Count + 2].Delete();
                    _doc.Tables[2].Rows[ds.Tables[1].Rows.Count + 2].Delete();

                    try
                    {

                        object filenamesave = GENERATED_FILE_NAME;
                        _doc.SaveAs2(ref filenamesave);
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (_doc != null)
                        {
                            _doc.Close(ref ob, ref ob, ref ob);
                            _doc = null;
                        }
                        if (_app != null)
                        {
                            _app.Quit(ref ob, ref ob, ref ob);
                            _app = null;
                        }

                    }

                    return File(GENERATED_FILE_NAME, "application/docx", filename);



                    //CrudModelDKH_KHDT _model = BindDataExport_KeHoachDaoTao(SearchModel.pKH_DVGTVT_MA, SearchModel.pKH_MA);
                    //byte[] contentFile = null;
                    //contentFile = GPS.ENTITYS.EXPORTWORD_KHOAHOC_KHDT.EXPORT_KHOAHOC_KHDT(Server.MapPath("~/Templates/GPS_Word/KeHoachVaLichThiSatHach.docx"), _model, "DocxGenericReport");
                    //string nameOutput = string.Format("KeHoachVaLichThiSatHach_{0}_{1}.docx", SearchModel.pKH_MA, DateTime.Now.ToShortDateString().Replace("/", "_"));
                    //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);


                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (_doc != null)
                    {
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                    }
                    if (_app != null)
                    {
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }
                }

            }


            else if (SearchModel.SearchButton == "Tìm kiếm")
            {
                if (!string.IsNullOrEmpty(SearchModel.pKH_DVGTVT_MA))
                {
                    model = GetListData(SearchModel);
                }
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                ViewBag.TotalRow = model == null ? "0" : model.Count().ToString();
                return View(SearchModel);
            }
            else
            {
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                ViewBag.TotalRow = model == null ? "0" : model.Count().ToString();
                return View(SearchModel);
            }
        }
        #endregion

        #region Detail
        public ActionResult _Detail(string id, string id1)
        {
            string _KH_MA = id;
            string _KH_DVGTVT_MA = id1;
            CrudModelFORMDKH _model = GetItemData(_KH_MA, _KH_DVGTVT_MA);
            return PartialView("_Detail", _model);
        }
        #endregion

        #region GetDataBinding
        public CrudModelDKH_KHDT BindDataExport_KeHoachDaoTao(string _KH_DVGTVT_MA, string _KH_MA)
        {
            CrudModelDKH_KHDT _model = new CrudModelDKH_KHDT();
            DataSet ds = Singleton<GPS_DAL.GPS_KhoaHocDAO>.Inst.GPS_DAOTAO_KHDT(_KH_DVGTVT_MA, _KH_MA);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                _model.KH_MA_TEN = ds.Tables[0].Rows[i]["KH_MA_TEN"] == null ? "" : ds.Tables[0].Rows[i]["KH_MA_TEN"].ToString();
                _model.KH_THOIGIANDT = ds.Tables[0].Rows[i]["KH_THOIGIANDT"] == null ? "" : ds.Tables[0].Rows[i]["KH_THOIGIANDT"].ToString();
                _model.KH_HANGGPLX_MA = ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"] == null ? "" : ds.Tables[0].Rows[i]["KH_HANGGPLX_MA"].ToString();
                _model.KH_TONGHV = ds.Tables[0].Rows[i]["KH_TONGHV"] == null ? "" : ds.Tables[0].Rows[i]["KH_TONGHV"].ToString();
            }
            _model.LIST_GioHoc = COMMON.Helpers.MapDataTableToList<CrudModelDKH_KHDT>(ds.Tables[1]);

            if (ds.Tables[2].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
                {
                    string strNgay = ds.Tables[2].Rows[i]["PHAN_GIAI_DOAN"].ToString();
                    if (strNgay == "02.Ngày")
                    {
                        ds.Tables[2].Rows[i]["PHAN_GIAI_DOAN"] = "Từ ngày - đến ngày";
                        //for (int j = 1; j <= 26; j++)
                        //{
                        //    string strDate;
                        //    if (j < 10)
                        //    {
                        //        strDate = ds.Tables[2].Rows[i][string.Format("W0{0}", j.ToString())] == null ? null : ds.Tables[2].Rows[i][string.Format("W0{0}", j.ToString())].ToString();
                        //        if (!string.IsNullOrEmpty(strDate.Trim()))
                        //        {
                        //            string[] arrListStr = strDate.Trim().Split(' ');
                        //            string date = "";
                        //            for (int g = 0; g < arrListStr.Length; g++)
                        //            {
                        //                DateTime element = DateTime.Parse(arrListStr[g].ToString());
                        //                date += element.ToString("dd/MM"); ;
                        //            }
                        //            ds.Tables[2].Rows[i][string.Format("W0{0}", j.ToString())] = date;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        strDate = ds.Tables[2].Rows[i][string.Format("W{0}", j.ToString())] == null ? null : ds.Tables[2].Rows[i][string.Format("W{0}", j.ToString())].ToString();
                        //        if (!string.IsNullOrEmpty(strDate.Trim()))
                        //        {
                        //            string[] arrListStr = strDate.Split(' ');
                        //            string date = "";
                        //            for (int g = 0; g < arrListStr.Length; g++)
                        //            {
                        //                DateTime element = DateTime.Parse(arrListStr[g].ToString());
                        //                date = element.ToString("dd/MM");
                        //            }
                        //            ds.Tables[2].Rows[i][string.Format("W{0}", j.ToString())] = date;
                        //        }
                        //    }
                        //}
                    }
                    else if (strNgay == "01.Tuần")
                    {
                        ds.Tables[2].Rows[i]["PHAN_GIAI_DOAN"] = "Tuần";
                    }
                    else if (strNgay == "00.Tháng")
                    {
                        ds.Tables[2].Rows[i]["PHAN_GIAI_DOAN"] = "Tháng";
                    }
                }
            }
            _model.LIST_LichHoc = COMMON.Helpers.MapDataTableToList<CrudModelDKH_KHDT>(ds.Tables[2]);
            _model.LIST_GVLThuyet = COMMON.Helpers.MapDataTableToList<CrudModelDKH_KHDT>(ds.Tables[3]);
            _model.LIST_GVThucHanh = COMMON.Helpers.MapDataTableToList<CrudModelDKH_KHDT>(ds.Tables[4]);
            return _model;
        }
        public List<CrudModelFORMDKH> GetListData(ViewModelDKH_DT SearchModel)
        {
            var param = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (SearchModel.pKH_MA == null ? DBNull.Value : (object)SearchModel.pKH_MA), ParameterDirection.Input);
            var param1 = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, (SearchModel.pKH_TEN == null ? DBNull.Value : (object)SearchModel.pKH_TEN), ParameterDirection.Input);
            var param2 = new OracleParameter("pKH_SOQDKG", OracleDbType.Varchar2, (SearchModel.pKH_SOQDKG == null ? DBNull.Value : (object)SearchModel.pKH_SOQDKG), ParameterDirection.Input);
            var param3 = new OracleParameter("pKH_DVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pKH_DVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pKH_DVGTVT_MA), ParameterDirection.Input);
            var param4 = new OracleParameter("pKH_NGAYQDKG_TU", OracleDbType.Date, (SearchModel.pKH_NGAYQDKG_TU == null ? DBNull.Value : (object)SearchModel.pKH_NGAYQDKG_TU), ParameterDirection.Input);
            var param5 = new OracleParameter("pKH_NGAYQDKG_DEN", OracleDbType.Date, (SearchModel.pKH_NGAYQDKG_DEN == null ? DBNull.Value : (object)SearchModel.pKH_NGAYQDKG_DEN), ParameterDirection.Input);
            var param6 = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<CrudModelFORMDKH>("BEGIN GPS_HOCVIEN.GPS_GETDS_SEARCH_KHOAHOC(:pKH_MA,:pKH_TEN,:pKH_SOQDKG,:pKH_DVGTVT_MA,:pKH_NGAYQDKG_TU, :pKH_NGAYQDKG_DEN,:pRET); end;", param, param1, param2, param3, param4, param5, param6).ToList();
            return entities;
        }
        public CrudModelFORMDKH GetItemData(string KH_MA, string KH_DVGTVT_MA)
        {
            var _pKH_MA = new OracleParameter("pKH_MA", OracleDbType.Varchar2, (KH_MA == null ? DBNull.Value : (object)KH_MA), ParameterDirection.Input);
            var _pKH_TEN = new OracleParameter("pKH_TEN", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pKH_SOQDKG = new OracleParameter("pKH_SOQDKG", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pKH_DVGTVT_MA = new OracleParameter("pKH_DVGTVT_MA", OracleDbType.Varchar2, (KH_DVGTVT_MA == null ? DBNull.Value : (object)KH_DVGTVT_MA), ParameterDirection.Input);
            var _pKH_NGAYQDKG_TU = new OracleParameter("pKH_NGAYQDKG_TU", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pKH_NGAYQDKG_DEN = new OracleParameter("pKH_NGAYQDKG_DEN", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<CrudModelFORMDKH>("BEGIN GPS_HOCVIEN.GPS_GETDS_SEARCH_KHOAHOC(:pKH_MA,:pKH_TEN,:pKH_SOQDKG,:pKH_DVGTVT_MA,:pKH_NGAYQDKG_TU, :pKH_NGAYQDKG_DEN,:pRET); end;"
                , _pKH_MA, _pKH_TEN, _pKH_SOQDKG, _pKH_DVGTVT_MA, _pKH_NGAYQDKG_TU, _pKH_NGAYQDKG_DEN, _pRET).FirstOrDefault();
            return entities;
        }
        #endregion

        #region ExportWord
        public ActionResult ExportWord(string id, string id1)
        {
            Microsoft.Office.Interop.Word.Application _app = null;
            Microsoft.Office.Interop.Word.Document _doc = null;
            object _pathFile = null;
            object ob = System.Reflection.Missing.Value;

            try
            {
                string _KH_MA = id;
                string _KH_DVGTVT_MA = id1;


                CrudModelFORMDKH _model = GetItemData(_KH_MA, _KH_DVGTVT_MA);


                //GiangTD3 - 2017-08-29: 
                string filename = "KhoaHoc_ThongTin_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/KhoaHoc_ThongTin_Template.dotx");
                var dicKHOAHOC = GPS.COMMON.Helpers.ToDictionary(_model);

                //khoi tao Word app:
                _pathFile = TEMPLATE_FILE_NAME;
                _app = new Microsoft.Office.Interop.Word.Application();
                _app.Visible = false;

                _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);



                foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                {
                    string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                    if (dicKHOAHOC.ContainsKey(fieldName))
                    {
                        field.Select();
                        if (dicKHOAHOC[fieldName] != null && !string.IsNullOrWhiteSpace(dicKHOAHOC[fieldName].ToString()))
                            _app.Selection.TypeText(dicKHOAHOC[fieldName].ToString());
                        else
                            _app.Selection.TypeText("  ");
                    }
                }

                try
                {

                    object filenamesave = GENERATED_FILE_NAME;
                    _doc.SaveAs2(ref filenamesave);
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (_doc != null)
                    {
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                    }
                    if (_app != null)
                    {
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }

                }

                return File(GENERATED_FILE_NAME, "application/docx", filename);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_doc != null)
                {
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                }
                if (_app != null)
                {
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
            }


            //byte[] contentFile = null;
            //contentFile = GPS.ENTITYS.EXPORTWORD_FORMDKH.EXPORT_FORMDKH(Server.MapPath("~/Templates/GPS_Word/KhoaHoc_ThongTin.docx"), _model, "DocxGenericReport");
            //string nameOutput = string.Format("ThongTinKhoaHoc_{0}_{1}.docx", _model.KH_TEN, DateTime.Now.ToShortDateString().Replace("/", "_"));
            //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
        }
        public ActionResult ExportWord_KHTDT(string id, string id1)
        {
            string _DVGTVT_MA = id;
            string _KH_MA = id1;
            Microsoft.Office.Interop.Word.Application _app = null;
            Microsoft.Office.Interop.Word.Document _doc = null;
            object _pathFile = null;
            object ob = System.Reflection.Missing.Value;

            try
            {

                //GiangTD3 - 2017-08-29: 
                string filename = "KeHoachDaoTao_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/KHDT_Template.dotx");

                //khoi tao Word app:
                _pathFile = TEMPLATE_FILE_NAME;
                _app = new Microsoft.Office.Interop.Word.Application();
                _app.Visible = false;

                _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);

                // DataSet ds = (new GPS_DAO()).GPS_DAOTAO_KHDT("40001", "40001K17B2037");
                DataSet ds = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_KHDT"
                                    , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_DVGTVT_MA) ? DBNull.Value : (object)_DVGTVT_MA, ParameterDirection.Input)
                                    , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_KH_MA) ? DBNull.Value : (object)_KH_MA, ParameterDirection.Input)
                                    , new OracleParameter("PRETHEADER", OracleDbType.RefCursor, ParameterDirection.Output)
                                    , new OracleParameter("PRETKHDT_GIOHOC", OracleDbType.RefCursor, ParameterDirection.Output)
                                    , new OracleParameter("PRETKHDT_LICHHOC", OracleDbType.RefCursor, ParameterDirection.Output)
                                    , new OracleParameter("PRETGVLTHUYET", OracleDbType.RefCursor, ParameterDirection.Output)
                                    , new OracleParameter("PRETGVTHUCHANH", OracleDbType.RefCursor, ParameterDirection.Output)
                                      );
                // thông tin chung khóa học
                if (ds.Tables[0].Rows.Count > 0)
                {
                    System.Data.DataTable vDataTable = ds.Tables[0];

                    Dictionary<string, string> dicKHOAHOC = new Dictionary<string, string>();
                    dicKHOAHOC.Add("DVGTVT_TEN", vDataTable.Rows[0]["DVGTVT_TEN"].ToString());
                    dicKHOAHOC.Add("KH_MA_TEN", vDataTable.Rows[0]["KH_MA_TEN"].ToString());
                    dicKHOAHOC.Add("KH_HANGGPLX_MA", vDataTable.Rows[0]["KH_HANGGPLX_MA"].ToString());
                    dicKHOAHOC.Add("KH_THOIGIANDT", vDataTable.Rows[0]["KH_THOIGIANDT"].ToString());
                    dicKHOAHOC.Add("KH_TONGHV", vDataTable.Rows[0]["KH_TONGHV"].ToString());

                    var _now = DateTime.Now;
                    dicKHOAHOC.Add("DD", (_now.Day < 10 ? "0" + _now.Day.ToString() : _now.Day.ToString()));
                    dicKHOAHOC.Add("MM", (_now.Month < 10 ? "0" + _now.Month.ToString() : _now.Month.ToString()));
                    dicKHOAHOC.Add("YYYY", _now.Year.ToString());

                    foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                    {
                        string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                        if (dicKHOAHOC.ContainsKey(fieldName))
                        {
                            field.Select();
                            _app.Selection.TypeText(dicKHOAHOC[fieldName]);
                        }
                    }
                }

                //Thông tin giờ học
                if (ds.Tables[1].Rows.Count > 0)
                {

                    System.Data.DataTable vDataTable = ds.Tables[1];
                    Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[2];

                    int lenrow = vDataTable.Rows.Count;
                    int lencol = vDataTable.Columns.Count;

                    for (int i = 0; i < lenrow; ++i)
                    {

                        tbl.Rows.Add(ref ob);

                        for (int j = 0; j < lencol; ++j)
                        {


                            tbl.Cell(i + 2, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                            tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                            tbl.Cell(i + 2, j + 1).Range.Font.Size = 10F;

                        }

                    }
                }

                //Thông tin phân phối lịch học
                if (ds.Tables[2].Rows.Count > 3)
                {

                    System.Data.DataTable vDataTable = ds.Tables[2];
                    int lenrow = vDataTable.Rows.Count;
                    int lencol = vDataTable.Columns.Count;

                    Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[3];

                    //Add column:
                    object c8 = System.Reflection.Missing.Value;
                    DataRow rTuNgayDenNgay = ds.Tables[2].Rows[0];
                    var lencol_temp = 0;
                    for (int i = 0; i < lencol; i++)
                    {
                        if (!rTuNgayDenNgay[i].Equals(DBNull.Value) && rTuNgayDenNgay[i] != null && !string.IsNullOrWhiteSpace(rTuNgayDenNgay[i].ToString()))
                        {
                            tbl.Columns.Add(ref c8);
                            lencol_temp++;

                        }
                    }
                    lencol = lencol_temp;

                    //fill data to 3 row đầu
                    var countmerge = 0;
                    var temp1 = "";
                    var _cellindex = 0;
                    var _temp = 0;
                    var _temp2 = 0;

                    for (int j = 1; j < lencol; j++)
                    {
                        temp1 = vDataTable.Rows[0][j].ToString();
                        if (j == 0)
                        {
                            _cellindex = j + 1 + _temp2;
                        }
                        else
                        {
                            _cellindex = j + 1 + _temp2;
                        }

                        if (!string.IsNullOrWhiteSpace(temp1))
                        {
                            if (j < lencol - 1)
                            {
                                for (int k = j + 1; k < lencol; k++)
                                {
                                    _temp = k;
                                    if (vDataTable.Rows[0][k].ToString() == temp1)
                                    {
                                        countmerge++;

                                    }
                                    else
                                    {
                                        break;

                                    }
                                }

                                _temp2 = _temp2 - countmerge;
                                if (countmerge > 0)
                                    tbl.Cell(1, _cellindex).Merge(tbl.Cell(1, countmerge + _cellindex));


                                tbl.Cell(1, _cellindex).Range.Text = temp1;
                                tbl.Cell(1, _cellindex).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(1, _cellindex).Range.Font.Size = 10F;
                                tbl.Cell(1, _cellindex).Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                countmerge = 0;
                                j = _temp - 1;

                            }
                            else
                            {
                                tbl.Cell(1, _cellindex).Range.Text = temp1;
                                tbl.Cell(1, _cellindex).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                                tbl.Cell(1, _cellindex).Range.Font.Size = 10F;
                                tbl.Cell(1, _cellindex).Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                // j++;
                            }
                        }


                    }

                    //fill 2 row tiếp theo:
                    for (int i = 1; i < 3; ++i)
                    {

                        for (int j = 1; j < lencol; ++j)
                        {


                            tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                            tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                            tbl.Cell(i + 1, j + 1).Range.Font.Size = 10F;

                        }

                    }

                    //fill còn lại
                    for (int i = 3; i < lenrow; ++i)
                    {

                        tbl.Rows.Add(ref ob);

                        for (int j = 0; j < lencol; ++j)
                        {


                            tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                            tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                            tbl.Cell(i + 1, j + 1).Range.Font.Size = 10F;

                        }

                    }
                }

                //thông tin lý thuyết
                if (ds.Tables[3].Rows.Count > 0)
                {

                    System.Data.DataTable vDataTable = ds.Tables[3];
                    Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[4];

                    int lenrow = vDataTable.Rows.Count;
                    int lencol = vDataTable.Columns.Count;

                    for (int i = 0; i < lenrow; ++i)
                    {
                        tbl.Rows.Add(ref ob);
                        for (int j = 0; j < lencol; ++j)
                        {
                            tbl.Cell(i + 1, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// + vDataTable.Rows[i][1].ToString();
                            tbl.Cell(i + 1, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                            tbl.Cell(i + 1, j + 1).Range.Font.Size = 11.5F;

                            tbl.Cell(i + 1, j + 1).Range.Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderBottom].LineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleDashDotDot;
                            tbl.Cell(i + 1, j + 1).Range.Borders[Microsoft.Office.Interop.Word.WdBorderType.wdBorderBottom].LineWidth = Microsoft.Office.Interop.Word.WdLineWidth.wdLineWidth050pt;
                        }




                    }

                }

                //thông tin thực hành
                if (ds.Tables[4].Rows.Count > 0)
                {

                    System.Data.DataTable vDataTable = ds.Tables[4];
                    Microsoft.Office.Interop.Word.Table tbl = _doc.Tables[5];

                    int lenrow = vDataTable.Rows.Count;
                    int lencol = vDataTable.Columns.Count;

                    for (int i = 0; i < lenrow; ++i)
                    {

                        tbl.Rows.Add(ref ob);

                        for (int j = 0; j < lencol; ++j)
                        {


                            tbl.Cell(i + 2, j + 1).Range.Text = vDataTable.Rows[i][j].ToString();// +"\v 2";
                            tbl.Cell(i + 2, j + 1).VerticalAlignment = Microsoft.Office.Interop.Word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                            tbl.Cell(i + 2, j + 1).Range.Font.Size = 11.5F;

                        }

                    }
                }


                try
                {

                    object filenamesave = GENERATED_FILE_NAME;
                    _doc.SaveAs2(ref filenamesave);
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (_doc != null)
                    {
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                    }
                    if (_app != null)
                    {
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }

                }

                return File(GENERATED_FILE_NAME, "application/docx", filename);



                //CrudModelDKH_KHDT _model = BindDataExport_KeHoachDaoTao(SearchModel.pKH_DVGTVT_MA, SearchModel.pKH_MA);
                //byte[] contentFile = null;
                //contentFile = GPS.ENTITYS.EXPORTWORD_KHOAHOC_KHDT.EXPORT_KHOAHOC_KHDT(Server.MapPath("~/Templates/GPS_Word/KeHoachVaLichThiSatHach.docx"), _model, "DocxGenericReport");
                //string nameOutput = string.Format("KeHoachVaLichThiSatHach_{0}_{1}.docx", SearchModel.pKH_MA, DateTime.Now.ToShortDateString().Replace("/", "_"));
                //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_doc != null)
                {
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                }
                if (_app != null)
                {
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
            }
        }

        public ActionResult ExportWord_DKSH(string id, string id1)
        {
            string _DVGTVT_MA = id;
            string _KH_MA = id1;
            Microsoft.Office.Interop.Word.Application _app = null;
            Microsoft.Office.Interop.Word.Document _doc = null;
            object _pathFile = null;
            object ob = System.Reflection.Missing.Value;

            try
            {

                //GiangTD3 - 2017-08-29: 
                string filename = "DangKySatHach_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".docx";
                String GENERATED_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_WordExport/" + filename);
                String TEMPLATE_FILE_NAME = System.Web.Hosting.HostingEnvironment.MapPath(@"~/Templates/GPS_Word/DKSHLX_Template.dotx");

                //khoi tao Word app:
                _pathFile = TEMPLATE_FILE_NAME;
                _app = new Microsoft.Office.Interop.Word.Application();
                _app.Visible = false;

                _doc = _app.Documents.Add(ref _pathFile, ref ob, ref ob, ref ob);

                // DataSet ds = (new GPS_DAO()).GPS_DAOTAO_KHDT("40001", "40001K17B2037");
                DataSet ds = OracleHelper.ExecuteDataset(GPS_OracleConnectHelper.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_KEHOACH_SATHACH"
                                    , new OracleParameter("PDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_DVGTVT_MA) ? DBNull.Value : (object)_DVGTVT_MA, ParameterDirection.Input)
                                    , new OracleParameter("PKH_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(_KH_MA) ? DBNull.Value : (object)_KH_MA, ParameterDirection.Input)
                                    , new OracleParameter("pRet1", OracleDbType.RefCursor, ParameterDirection.Output)
                                    , new OracleParameter("pRet2", OracleDbType.RefCursor, ParameterDirection.Output)

                                      );
                // thông tin chung DTVTVT
                if (ds.Tables[0].Rows.Count > 0)
                {
                    System.Data.DataTable vDataTable = ds.Tables[0];

                    Dictionary<string, string> dicKHOAHOC = new Dictionary<string, string>();
                    dicKHOAHOC.Add("DVGTVT_TEN", vDataTable.Rows[0]["DVGTVT_TEN"].ToString());
                    dicKHOAHOC.Add("KH_MA_TEN", vDataTable.Rows[0]["KH_MA_TEN"].ToString());
                    dicKHOAHOC.Add("KH_HANGGPLX_MA", vDataTable.Rows[0]["KH_HANGGPLX_MA"].ToString());
                    dicKHOAHOC.Add("KH_THOIGIANDT", vDataTable.Rows[0]["KH_THOIGIANDT"].ToString());
                    dicKHOAHOC.Add("KH_TONGHV", vDataTable.Rows[0]["KH_TONGHV"].ToString());

                    var _now = DateTime.Now;
                    dicKHOAHOC.Add("DD", (_now.Day < 10 ? "0" + _now.Day.ToString() : _now.Day.ToString()));
                    dicKHOAHOC.Add("MM", (_now.Month < 10 ? "0" + _now.Month.ToString() : _now.Month.ToString()));
                    dicKHOAHOC.Add("YYYY", _now.Year.ToString());

                    foreach (Microsoft.Office.Interop.Word.Field field in _doc.Fields)
                    {
                        string fieldName = field.Code.Text.Substring(11, field.Code.Text.IndexOf("\\") - 12).Trim();
                        if (dicKHOAHOC.ContainsKey(fieldName))
                        {
                            field.Select();
                            _app.Selection.TypeText(dicKHOAHOC[fieldName]);
                        }
                    }
                }



                try
                {

                    object filenamesave = GENERATED_FILE_NAME;
                    _doc.SaveAs2(ref filenamesave);
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    if (_doc != null)
                    {
                        _doc.Close(ref ob, ref ob, ref ob);
                        _doc = null;
                    }
                    if (_app != null)
                    {
                        _app.Quit(ref ob, ref ob, ref ob);
                        _app = null;
                    }

                }

                return File(GENERATED_FILE_NAME, "application/docx", filename);



                //CrudModelDKH_KHDT _model = BindDataExport_KeHoachDaoTao(SearchModel.pKH_DVGTVT_MA, SearchModel.pKH_MA);
                //byte[] contentFile = null;
                //contentFile = GPS.ENTITYS.EXPORTWORD_KHOAHOC_KHDT.EXPORT_KHOAHOC_KHDT(Server.MapPath("~/Templates/GPS_Word/KeHoachVaLichThiSatHach.docx"), _model, "DocxGenericReport");
                //string nameOutput = string.Format("KeHoachVaLichThiSatHach_{0}_{1}.docx", SearchModel.pKH_MA, DateTime.Now.ToShortDateString().Replace("/", "_"));
                //return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);


            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_doc != null)
                {
                    _doc.Close(ref ob, ref ob, ref ob);
                    _doc = null;
                }
                if (_app != null)
                {
                    _app.Quit(ref ob, ref ob, ref ob);
                    _app = null;
                }
            }
        }

        #endregion

        #region"Ajax"
        [HttpPost]
        public ActionResult getKH_MAs(string MaDVGT, string ma_DVGT)
        {
            string _ma_DVGT = ma_DVGT.Split(':')[0];

            string _YY_NOW = DateTime.Now.Year.ToString().Substring(2, 2);
            string strKH_MA = _ma_DVGT + "K" + _YY_NOW + "01";
            var KH_MAX = _db.Filter<DKH>(o => o.KH_DVGTVT_MA == _ma_DVGT).OrderByDescending(o => o.KH_MA).FirstOrDefault();
            if (KH_MAX != null)
            {
                var _YY_MAX = KH_MAX.KH_MA.Substring(_ma_DVGT.Length + 1, 2);
                if (_YY_NOW == _YY_MAX)
                {
                    var _maxNumber = KH_MAX.KH_MA.Substring((_ma_DVGT + "K" + _YY_MAX).Length, 2);
                    strKH_MA = _ma_DVGT + "K" + _YY_NOW + ((int.Parse(_maxNumber) + 1) < 10 ? "0" + (int.Parse(_maxNumber) + 1).ToString() : (_maxNumber + 1).ToString());
                }
            }
            return Content(string.Join("", strKH_MA));
        }
        public JsonResult AutoCompleteDVGTVT_MA(string term)
        {
            var result = _db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40" && (o.DVGTVT_MA.Contains(term) || o.DVGTVT_TEN.ToUpper().Contains(term.ToUpper())));
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        public JsonResult AutoCompleteKH_MA(string term, string dvgtvt_ma)
        {
            string _ma_DVGT = dvgtvt_ma.Split(':')[0];
            var result = Singleton<GPS_HocVienDAO>.Inst.GPS_GETDS_KHOAHOC_HOCVIEN1(_ma_DVGT, term);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        //GiangTD3:

        public ActionResult Select2()
        {
            return View();
        }
        public JsonResult Select2KH_MA(string searchTerm, string dvgtvt_ma)
        {
            var result = new Select2PagedResult();
            if (!string.IsNullOrWhiteSpace(searchTerm))
            {
                string _ma_DVGT = dvgtvt_ma;
                var khoahocs = Singleton<GPS_HocVienDAO>.Inst.GPS_GETDS_KHOAHOC_HOCVIEN1(_ma_DVGT, searchTerm).Take(20).ToList();
                result.Results = new List<Select2OptionModel>();
                khoahocs.ForEach(o =>
                    {
                        result.Results.Add(new Select2OptionModel() { id = o.KH_MA, text = o.KH_TEN });
                    });
            }

            return Json(result, JsonRequestBehavior.AllowGet);

            // return Json(result, JsonRequestBehavior.AllowGet);
        }

        #endregion

        #region Disable
        #region Create
        //public ActionResult Create()
        //{
        //    initialCategoryCreateAction();

        //    return View(new CrudModelDKH());
        //}

        //[HttpPost]
        //public ActionResult Create(CrudModelDKH model)
        //{
        //    if (ModelState.IsValid)
        //    {
        //        DKH _model = new DKH();
        //        GPS.COMMON.Helpers.CopyObject<DKH>(model, ref _model);
        //        _model.ID = EFSequence.GetNextValue("SEQ_DKH_ID");
        //        if (!string.IsNullOrWhiteSpace(model.KH_DVGTVT_MA))
        //        {
        //            _model.KH_DVGTVT_MA = model.KH_DVGTVT_MA.Split(':')[0];
        //        }

        //        _model.KH_NGUOINHAP = User.Identity.Name;
        //        _model.KH_NGAYNHAP = DateTime.Now;

        //        _db.Insert<DKH>(_model);
        //        return RedirectToAction("Index");
        //    }

        //    return View(model);
        //}
        //public void initialCategoryCreateAction()
        //{
        //    ViewBag.DVGTVT_MAs = new SelectList(_db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");

        //}
        #endregion

        #region Edit
        //public ActionResult Edit(decimal id)
        //{

        //    DKH model = _db.GetOne<DKH>(o => o.ID == id);

        //    CrudModelDKH _model = new CrudModelDKH();
        //    GPS.COMMON.Helpers.CopyObject<CrudModelDKH>(model, ref _model, new List<string> { "KH_ACTIVE" });

        //    var dvgtvt = _db.GetOne<SDONVIGTVT>(o => o.DVGTVT_MA == model.KH_DVGTVT_MA);
        //    _model.KH_DVGTVT_MA = dvgtvt.DVGTVT_MA + ":" + dvgtvt.DVGTVT_TEN;

        //    initialCategoryEditAction(_model);
        //    return View(_model);
        //}

        // [HttpPost]
        //public ActionResult Edit(CrudModelDKH model)
        //{
        //    if (ModelState.IsValid)
        //    {
        //        var _model = _db.GetOne<DKH>(o => o.ID == model.ID);
        //        GPS.COMMON.Helpers.CopyObject<DKH>(model, ref _model, new List<string> { "KH_ACTIVE", "KH_NGUOINHAP", "KH_NGAYNHAP" });

        //        if (!string.IsNullOrWhiteSpace(model.KH_DVGTVT_MA))
        //        {
        //            _model.KH_DVGTVT_MA = model.KH_DVGTVT_MA.Split(':')[0];
        //        }

        //        _model.KH_NGUOISUA = User.Identity.Name;
        //        _model.KH_NGAYSUA = DateTime.Now;

        //        _db.Update<DKH>(_model);
        //        return RedirectToAction("Index");
        //    }

        //    return View(model);
        //}


        //public void initialCategoryEditAction(CrudModelDKH view)
        //{
        //  //  ViewBag.DVGTVT_MAs = new SelectList(_db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");

        //}
        #endregion

        #region Delete
        // public ActionResult Delete(decimal id)
        //{
        //    CrudModelDKH _model = new CrudModelDKH();
        //    DKH model = _db.GetOne<DKH>(o => o.ID == id);
        //    _db.DeleteItem<DKH>(model);
        //    return RedirectToAction("Index");
        //}
        #endregion
        #endregion

        public Int16 contant_Cell(Int16 thang)
        {
            Int16 cell = 0;
            switch (thang)
            {
                case 1: cell = 5; break;
                case 2: cell = 9; break;
                case 3: cell = 13; break;
                case 4: cell = 17; break;
                case 5: cell = 21; break;
                case 6: cell = 25; break;
                case 7: cell = 29; break;
                case 8: cell = 33; break;
                case 9: cell = 37; break;
                case 10: cell = 41; break;
                case 11: cell = 45; break;
                case 12: cell = 49; break;
                default:
                    break;
            }
            return cell;
        }
    }

}